package com.wsw.common.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import net.sourceforge.pinyin4j.format.exception.BadHanyuPinyinOutputFormatCombination;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

/**
 * <p>
 * ExcelUtil.java
 * </p>
 * <p>
 * </p>
 * 
 * @author $Author: sw.wang $
 * @version $Revision: V1.0 $
 */
public class ExcelUtil {

	/**
	 * 加载excel(基本支持所有)
	 * <p>
	 * </p>
	 * 
	 * @param file
	 * @return
	 */
	public static Workbook loadModelWorkBookExcel(String path)
			throws FileNotFoundException, IOException {
		FileInputStream fis = new FileInputStream(new File(path));
		Workbook workbook = null;
		try {
			workbook = WorkbookFactory.create(fis);
		} catch (InvalidFormatException e) {
			e.printStackTrace();
		}
		return workbook;
	}

	/**
	 * 加载excel(小数据量 不支持xlsx)
	 * <p>
	 * </p>
	 * 
	 * @param file
	 * @return
	 */
	public static HSSFWorkbook loadModelExcel(String path)
			throws FileNotFoundException, IOException {
		FileInputStream fis = new FileInputStream(new File(path));
		HSSFWorkbook workbook = new HSSFWorkbook(fis);
		return workbook;
	}

	/**
	 * 读取excel
	 * <p>
	 * </p>
	 * 
	 * @param path path
	 * @param colTitleLen 多少列
	 * @throws IOException
	 * @throws FileNotFoundException
	 */
	public static List<HashMap<String, String>> readExcel(String path,
			int colTitleLen, int SheetAt) throws FileNotFoundException, IOException {
		Workbook workbook = loadModelWorkBookExcel(path);
		Sheet sheet = workbook.getSheetAt(SheetAt);
		List<HashMap<String, String>> lists = new ArrayList<HashMap<String, String>>();
		HashMap<String, String> hashMap = null;
		Row r = sheet.getRow(1);
		String colTitle[] = createColTitle(r, colTitleLen);
		for (int i = 0; i < sheet.getLastRowNum(); i++) {
			Row row = sheet.getRow(i + 2);
			hashMap = new HashMap<String, String>();
			try {
				for (int j = 0; j < colTitleLen; j++) {
					Cell cell = row.getCell(j);
					hashMap.put(colTitle[j], getCellValue(cell));
				}
			} catch (Exception e) {
				lists.size();
			}
			lists.add(hashMap);
		}
		return lists;
	}

	private static String[] createColTitle(Row row, int colTitleLen) {
		String cols[] = new String[colTitleLen];
		for (int i = 0; i < colTitleLen; i++) {
			try {
				cols[i] = PinYinUtil.findPinYin(getCellValue(row.getCell(i)));
			} catch (BadHanyuPinyinOutputFormatCombination e) {
				e.printStackTrace();
			}
		}
		return cols;
	}

	/**
	 * 由于Excel当中的单元格Cell存在类型,若获取类型错误就会产生异常, 所以通过此方法将Cell内容全部转换为String类型
	 * 
	 * @param cell
	 * @return
	 */
	public static String getCellValue(Cell cell) {
		String str = null;
		switch (cell.getCellType()) {
		case Cell.CELL_TYPE_BLANK:
			str = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:
			str = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_FORMULA:
			str = String.valueOf(cell.getCellFormula());
			break;
		case Cell.CELL_TYPE_NUMERIC:
			str = String.valueOf((long) cell.getNumericCellValue());
			break;
		case Cell.CELL_TYPE_STRING:
			str = String.valueOf(cell.getStringCellValue());
			break;
		default:
			str = null;
			break;
		}
		return str;
	}

	public static void main(String[] args) throws IOException {
		/*
		 * try { List<HashMap<String, String>> maps =
		 * readExcel("g://该品目没有包的项目.xls", 3); for (HashMap<String, String>
		 * hashMap : maps) { Iterator iterator = hashMap.keySet().iterator();
		 * while(iterator.hasNext()){ String name = iterator.next().toString();
		 * System.out.print(name + "\t" + hashMap.get(name)); }
		 * System.out.println(); } } catch (IOException e) {
		 * e.printStackTrace(); }
		 */

		Workbook workbook = loadModelWorkBookExcel("g://test.xls");
		Sheet sheet = workbook.getSheetAt(0);
		Cell cell = null;
		for (int i = 109; i < 65535; i++) {
			Row row = sheet.createRow(i);
			cell = row.createCell(0);
			cell.setCellValue("wsw" + i);
			cell = row.createCell(1);
			cell.setCellValue("zxc" + i);
		}
		FileOutputStream out = new FileOutputStream("g://1.xlsx");
		workbook.write(out);
		out.close();
	}
}
